

/**
	AUTHOR		: GANDHI
	DATE		: 03 MARCH, 2005
	DESCRIPTION	: THIS PROC IS USED TO GET ALL FORECAST,BACKLOG AND SHIPPING DETAILS BASED ON SEARCH CRITERIA. 
*/

			

ALTER                   PROC sp_forecast_backlog_report(
 
 @CUSTOMER_INPUT VARCHAR(100),
 @PROJECT_INPUT VARCHAR(100),
 @PRODUCT_INPUT VARCHAR(100),
 @ODM_INPUT VARCHAR(100),
 @MONTH_FROM INT,
 @MONTH_TO INT,
 @YEAR INT, 
 @REPORT_ON VARCHAR(50),
 @defaultPrompt varchar(20))
 
 -- @MONTH_FROM CONTAINS START MONTH OR START QUARTER DEPENDING UP ON FORECAST SETUP
 -- @MONTH_TO CONTAINS NEXT NUMBER OF MONTHS OR QUARTERS OR YEARS, REPORT TO BE SHOWN
 
 AS
 BEGIN
	
	
	DECLARE @RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE FLOAT , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, BL_QUANTITY INT, BL_PRICE FLOAT, ACTUAL_QUANTITY INT, ACTUAL_PRICE FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50)  )
	DECLARE @SPERSON_RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE FLOAT , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, BL_QUANTITY INT, BL_PRICE FLOAT, ACTUAL_QUANTITY INT, ACTUAL_PRICE FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50) )
	DECLARE @BL_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, BL_QUANTITY INT, BL_PRICE FLOAT , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT )	
	DECLARE @SHIP_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, ACTUAL_QUANTITY INT, ACTUAL_PRICE FLOAT , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT )		
	DECLARE @TEMP_RESULTS TABLE( SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), YEAR INT, FC_QUANTITY INT, FC_PRICE FLOAT , CONFIDENCE_LEVEL FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50) )
	DECLARE @TOTAL_RESULTS TABLE( CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE FLOAT, CONFIDENCE_LEVEL FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50) )	
	DECLARE @BUILD_RESULTS TABLE( BUILDNO VARCHAR(50), SPERSON_ID INT, CUSTOMER VARCHAR(100), PROJECT  VARCHAR(100), PRODUCT VARCHAR(100), ODM VARCHAR(100), MONTH INT, QUARTER INT, YEAR INT, FC_QUANTITY INT, FC_PRICE FLOAT , USERLIST1 VARCHAR(100), USERDATE1 VARCHAR(100), USERTXT1 VARCHAR(100), USERTXT2 VARCHAR(100), CONFIDENCE_LEVEL FLOAT, BL_QUANTITY INT, BL_PRICE FLOAT, ACTUAL_QUANTITY INT, ACTUAL_PRICE FLOAT, CUSTID INT, PROJID INT, PRODID INT, ODMID INT, FORECAST_TYPE VARCHAR(50)  )
   	
	DECLARE @BYPERIOD VARCHAR(50)
	SELECT @BYPERIOD = TFNAME FROM FCTFRAME WHERE TFSELECT = 0

	SET @MONTH_TO = 3     -- SHOW NEXT RESTRICTED TO 3 MONTHS FOR STATIC REPORTS	

	DECLARE @TO_MONTH INT	
	DECLARE @YEAR_TO INT
	IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'MONTH' 
	BEGIN

		IF RTRIM(@MONTH_FROM) = -1
	 	BEGIN
	 		SET @MONTH_FROM = 0
	 	END
	 
	 	IF RTRIM(@MONTH_TO) = -1
	 	BEGIN
	 		SET @MONTH_TO = 11
	 	END
	 	
	 	SET @TO_MONTH = @MONTH_FROM + @MONTH_TO 
	 
	 	SET @YEAR_TO = @YEAR + @TO_MONTH/12 
	 	SET @MONTH_TO = @TO_MONTH%12	
	END
	IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'QUARTER'
	BEGIN 		
	
		IF RTRIM(@MONTH_FROM) = -1
	 	BEGIN
	 		SET @MONTH_FROM = 0
	 	END
	 
	 	IF RTRIM(@MONTH_TO) = -1
	 	BEGIN
	 		SET @MONTH_TO = 3
	 	END
	 	
	 	SET @TO_MONTH = @MONTH_FROM + @MONTH_TO 
	 
		SET @YEAR_TO = @YEAR + @TO_MONTH/4 
 		SET @MONTH_TO = @TO_MONTH%4	
	END
	IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR'
	BEGIN 		
		SET @YEAR_TO = @YEAR + @MONTH_TO
	END
	
	DECLARE @SF_SPID INT
	DECLARE @SF_CUSTOMER VARCHAR(100)
 	DECLARE @SF_PRODUCT VARCHAR(50)
 	DECLARE @SF_PROJECT VARCHAR(50)
 	DECLARE @SF_ODM VARCHAR(100)
	DECLARE @SF_CUSTOMER_NAME VARCHAR(100)
 	DECLARE @SF_PRODUCT_NAME VARCHAR(50)
 	DECLARE @SF_PROJECT_NAME VARCHAR(50)
 	DECLARE @SF_ODM_NAME VARCHAR(100)
 	DECLARE @SF_MONTH INT
	DECLARE @SF_QUARTER INT
 	DECLARE @SF_YEAR INT
 	DECLARE @SF_QUANTITY INT
	DECLARE @SF_PRICE FLOAT
 	DECLARE @SF_USERLIST1 VARCHAR(100)
	DECLARE @SF_USERDATE1 VARCHAR(100)
	DECLARE @SF_USERTXT1 VARCHAR(100)
	DECLARE @SF_USERTXT2 VARCHAR(100)
	DECLARE @SF_CONFIDENCE_LEVEL FLOAT
	DECLARE @MKTG_PART_NUMBER VARCHAR(100)
	
	DECLARE @SPERSON_SPID INT
	DECLARE @PRODUCT_SEARCH VARCHAR(100)
	DECLARE @CUSTOMER_SEARCH VARCHAR(100)
	SET @PRODUCT_SEARCH = @PRODUCT_INPUT
	SET @CUSTOMER_SEARCH = @CUSTOMER_INPUT
	DECLARE @SF_FORECAST_TYPE VARCHAR(50)
	DECLARE @SF_BUILDNO VARCHAR(50)

 	DECLARE @SPERSON_CURSOR CURSOR 
	

	IF @CUSTOMER_INPUT IS NULL  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = ''  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-1'  OR  LTRIM(RTRIM(@CUSTOMER_INPUT)) = '-2' 
	BEGIN
		SET @CUSTOMER_INPUT = '0'
	END
	IF @PROJECT_INPUT IS NULL  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = ''  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PROJECT_INPUT)) = '-2'
	BEGIN
		SET @PROJECT_INPUT = '0'
	END
	IF @PRODUCT_INPUT IS NULL  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = ''  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-1'  OR  LTRIM(RTRIM(@PRODUCT_INPUT)) = '-2'
	BEGIN
		SET @PRODUCT_INPUT = '0'
	END
	IF @ODM_INPUT IS NULL  OR  LTRIM(RTRIM(@ODM_INPUT)) = ''  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-1'  OR  LTRIM(RTRIM(@ODM_INPUT)) = '-2'
	BEGIN
		SET @ODM_INPUT = '0'
	END	
	 	
 	DECLARE @SQLSTRING NVARCHAR(3000)
	DECLARE @PARAMDEFINITION NVARCHAR(500)

	IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
	BEGIN
		SET @SQLSTRING = N'DECLARE FORECASTCURSOR CURSOR FOR SELECT NULL, SPERSONID, CUSTID, PROJID, PRODID, ODMID, MONTH, QUARTER, YEAR, QTY, QTY*PRICE, USERLIST1, USERDATE1, USERTXT1, USERTXT2, CONFIDENCE_LEVEL, ''SALES'' FROM FCENTRYMASTER WHERE ROWID IS NOT NULL '
		SET @PARAMDEFINITION = N'@PRM_CUSTOMER_INPUT VARCHAR(100), @PRM_PROJECT_INPUT VARCHAR(100), @PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @MONTH_FROM INT, @MONTH_TO INT, @TO_MONTH INT, @YEAR INT, @YEAR_TO INT '
				
		IF @CUSTOMER_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CUSTID = @PRM_CUSTOMER_INPUT '
		END
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @PROJECT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PROJID = @PRM_PROJECT_INPUT '
		END	
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'MONTH' 
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( ( MONTH >= @MONTH_FROM AND YEAR = @YEAR AND MONTH < @TO_MONTH )	OR
 		      		( MONTH < @MONTH_TO AND YEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
			      		( YEAR < @YEAR_TO AND YEAR > @YEAR ) ) '
		
		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'QUARTER'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( ( QUARTER >= @MONTH_FROM AND YEAR = @YEAR AND QUARTER < @TO_MONTH )	OR
 		      		( QUARTER < @MONTH_TO AND YEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
			     		 ( YEAR < @YEAR_TO AND YEAR > @YEAR ) )	'
		END
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( YEAR >= @YEAR AND YEAR < @YEAR_TO ) '
		END		

		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_CUSTOMER_INPUT = @CUSTOMER_INPUT, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_PROJECT_INPUT = @PROJECT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT, @MONTH_FROM = @MONTH_FROM, @MONTH_TO = @MONTH_TO, @TO_MONTH = @TO_MONTH, @YEAR = @YEAR, @YEAR_TO = @YEAR_TO
	END
	ELSE 
	BEGIN
		
		SET @SQLSTRING = N'DECLARE FORECASTCURSOR CURSOR FOR SELECT BUILDNO, SPERSONID, CUSTID, PROJID, PRODID, ODMID, MONTH, QUARTER, YEAR, QTY, QTY*PRICE, USERLIST1, USERDATE1, USERTXT1, USERTXT2, 0, ''BUILD'' FROM FCBUILDMASTER WHERE ROWID IS NOT NULL '
		SET @PARAMDEFINITION = N'@PRM_CUSTOMER_INPUT VARCHAR(100), @PRM_PROJECT_INPUT VARCHAR(100), @PRM_PRODUCT_INPUT VARCHAR(100), @PRM_ODM_INPUT VARCHAR(100), @MONTH_FROM INT, @MONTH_TO INT, @TO_MONTH INT, @YEAR INT, @YEAR_TO INT '
		
		IF @CUSTOMER_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND CUSTID = @PRM_CUSTOMER_INPUT '
		END
		IF @PRODUCT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PRODID = @PRM_PRODUCT_INPUT '
		END
		IF @PROJECT_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND PROJID = @PRM_PROJECT_INPUT '
		END	
		IF @ODM_INPUT <> '0'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ODMID = @PRM_ODM_INPUT '
		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'MONTH' 
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( ( MONTH >= @MONTH_FROM AND YEAR = @YEAR AND MONTH < @TO_MONTH )	OR
 		      		( MONTH < @MONTH_TO AND YEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
			      		( YEAR < @YEAR_TO AND YEAR > @YEAR ) ) '
		
		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'QUARTER'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( ( QUARTER >= @MONTH_FROM AND YEAR = @YEAR AND QUARTER < @TO_MONTH )	OR
 		      		( QUARTER < @MONTH_TO AND YEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
			     		 ( YEAR < @YEAR_TO AND YEAR > @YEAR ) )	'
		END
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR'
		BEGIN
			SET @SQLSTRING = @SQLSTRING + ' AND ( YEAR >= @YEAR AND YEAR < @YEAR_TO ) '
		END		

		EXECUTE SP_EXECUTESQL @SQLSTRING, @PARAMDEFINITION, @PRM_CUSTOMER_INPUT = @CUSTOMER_INPUT, @PRM_PRODUCT_INPUT = @PRODUCT_INPUT, @PRM_PROJECT_INPUT = @PROJECT_INPUT, @PRM_ODM_INPUT = @ODM_INPUT, @MONTH_FROM = @MONTH_FROM, @MONTH_TO = @MONTH_TO, @TO_MONTH = @TO_MONTH, @YEAR = @YEAR, @YEAR_TO = @YEAR_TO
	END


		
 	IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN 
			SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN  
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT ))*/
		END
   	END
 	
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT ))*/
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN
	 	IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
			SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PROJID = @PROJECT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PROJID = @PROJECT_INPUT ))*/
		END
	END	
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE ODMID = @ODM_INPUT ))*/
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT ))*/
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PROJID = @PROJECT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PROJID = @PROJECT_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 	
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 
 	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT <> '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PROJID = @PROJECT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
	
	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT <> '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND ODMID = @ODM_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND ODMID = @ODM_INPUT ))*/
		END
	END
 	
	ELSE IF @CUSTOMER_INPUT <> '0' AND @PRODUCT_INPUT <> '0' AND @PROJECT_INPUT <> '0' AND @ODM_INPUT = '0'
 	BEGIN
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT )
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER WHERE CUSTID = @CUSTOMER_INPUT AND PRODID = @PRODUCT_INPUT AND PROJID = @PROJECT_INPUT ))*/
		END
	END

 	ELSE IF @CUSTOMER_INPUT = '0' AND @PRODUCT_INPUT = '0' AND @PROJECT_INPUT = '0' AND @ODM_INPUT = '0'
 	BEGIN	
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
	 		SET @SPERSON_CURSOR = CURSOR FOR
			SELECT FCSPERSONREL.SPERSONID FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER)
			AND LEVELORDER = ( SELECT MAX(LEVELORDER) FROM FCLEVEL WHERE LEVELSELECT = 0 )
			/*(SELECT MAX(LEVELORDER) FROM FCSPERSONREL WHERE 
			FCSPERSONREL.SPERSONID IN (SELECT SPERSONID FROM FCENTRYMASTER))*/
		END
	END
 
		OPEN FORECASTCURSOR 
 			FETCH NEXT FROM FORECASTCURSOR
 			INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE
 			WHILE @@FETCH_STATUS = 0
 			BEGIN

				SET @SF_CUSTOMER_NAME = NULL
				SET @SF_PROJECT_NAME = NULL
				SET @SF_PRODUCT_NAME = NULL
				SET @SF_ODM_NAME = NULL
				SELECT @SF_CUSTOMER_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_CUSTOMER
				SELECT @SF_PROJECT_NAME = PROJNAME FROM  PROJSETUP WHERE PROJID = @SF_PROJECT
				SELECT @SF_PRODUCT_NAME = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @SF_PRODUCT
				SELECT @SF_ODM_NAME = C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = @SF_ODM
 				 								
				INSERT INTO @RESULTS VALUES( @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER_NAME, @SF_PROJECT_NAME, @SF_PRODUCT_NAME, @SF_ODM_NAME, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, 0, 0.0, 0, 0.0, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_FORECAST_TYPE )
 				 					
 				FETCH NEXT FROM FORECASTCURSOR
 				INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE				
 			END	
 		 																
 		CLOSE FORECASTCURSOR
 		--DEALLOCATE FORECASTCURSOR 

		
		IF @REPORT_ON IS NOT NULL AND LTRIM(RTRIM(@REPORT_ON)) = 'SALES'
		BEGIN	
			OPEN @SPERSON_CURSOR 
	 			FETCH NEXT FROM @SPERSON_CURSOR
	 			INTO @SPERSON_SPID
	 			WHILE @@FETCH_STATUS = 0
	 			BEGIN
	
					INSERT INTO @SPERSON_RESULTS SELECT * FROM @RESULTS WHERE SPERSON_ID = @SPERSON_SPID
					 										
	 				FETCH NEXT FROM @SPERSON_CURSOR
	 				INTO @SPERSON_SPID
	 			END	
	 				DELETE FROM @RESULTS
					INSERT INTO @RESULTS SELECT * FROM @SPERSON_RESULTS	
					IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR'
					BEGIN
						INSERT INTO @TEMP_RESULTS SELECT SPERSON_ID, CUSTOMER, PROJECT, PRODUCT, ODM, YEAR, SUM(FC_QUANTITY) FC_QUANTITY, SUM(FC_PRICE) FC_PRICE, CONFIDENCE_LEVEL, CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE FROM @RESULTS
						GROUP BY SPERSON_ID, CUSTOMER, CUSTID, PROJECT, PROJID, PRODUCT, PRODID, ODM, ODMID, CONFIDENCE_LEVEL, FORECAST_TYPE, YEAR
						DELETE FROM @RESULTS
						INSERT INTO @RESULTS SELECT NULL, SPERSON_ID, CUSTOMER, PROJECT, PRODUCT, ODM, NULL, NULL, YEAR, FC_QUANTITY, FC_PRICE,NULL, NULL, NULL, NULL, CONFIDENCE_LEVEL, 0, 0, 0, 0, CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE FROM @TEMP_RESULTS	
					END
					INSERT INTO @TOTAL_RESULTS SELECT  CUSTOMER, PROJECT, PRODUCT, ODM, MONTH, QUARTER, YEAR, SUM(FC_QUANTITY) FC_QUANTITY, SUM(FC_PRICE) FC_PRICE, AVG(CONFIDENCE_LEVEL) CONFIDENCE_LEVEL, CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE  FROM @RESULTS				
					GROUP BY CUSTOMER, CUSTID, PROJECT, PROJID, PRODUCT, PRODID, ODM, ODMID, FORECAST_TYPE, MONTH, QUARTER, YEAR
					DELETE FROM @RESULTS
					INSERT INTO @RESULTS SELECT NULL, NULL, CUSTOMER, PROJECT, PRODUCT, ODM, MONTH, QUARTER, YEAR, FC_QUANTITY, FC_PRICE,NULL, NULL, NULL, NULL, CONFIDENCE_LEVEL, 0, 0, 0, 0, CUSTID, PROJID, PRODID, ODMID, FORECAST_TYPE FROM @TOTAL_RESULTS					
	
					UPDATE @RESULTS SET ODM = '' WHERE ODM IS NULL												
	
	 		CLOSE @SPERSON_CURSOR
	 		DEALLOCATE @SPERSON_CURSOR 
		END
		ELSE
		BEGIN
			DECLARE @BUILD_CURSOR CURSOR
			DECLARE @BUILD_NO VARCHAR(50)

			SET @BUILD_CURSOR = CURSOR FOR
			SELECT DISTINCT 'BFC-'+CONVERT(VARCHAR,(MAX(CONVERT(INT,SUBSTRING(BUILDNO, 5, LEN(BUILDNO)))))) BUILDNO FROM @RESULTS GROUP BY CUSTOMER,PROJECT,PRODUCT,ODM,MONTH,QUARTER,YEAR
			
			OPEN @BUILD_CURSOR 
	 			FETCH NEXT FROM @BUILD_CURSOR
	 			INTO @BUILD_NO

	 			WHILE @@FETCH_STATUS = 0
	 			BEGIN	
					INSERT INTO @BUILD_RESULTS SELECT * FROM @RESULTS WHERE BUILDNO = @BUILD_NO AND SPERSON_ID IS NULL	
					
					FETCH NEXT FROM @BUILD_CURSOR
					INTO @BUILD_NO

				END
					DELETE FROM @RESULTS
					INSERT INTO @RESULTS SELECT * FROM @BUILD_RESULTS

					UPDATE @RESULTS SET ODM = '' WHERE ODM IS NULL
			
			CLOSE @BUILD_CURSOR
			DEALLOCATE @BUILD_CURSOR

		END
 							
	

		-- FOR BACKLOG DETAILS


		DECLARE @AS_OF_DATE VARCHAR(20)
	 	SET @AS_OF_DATE = CONVERT(VARCHAR, GETDATE(), 101)
		DECLARE @BL_CUSTOMER VARCHAR(100)
		DECLARE @BL_END_USER VARCHAR(100)
	 	DECLARE @BL_PRODUCT VARCHAR(50)
		DECLARE @BL_MONTH INT
		DECLARE @BL_QUARTER INT
	 	DECLARE @BL_YEAR INT
	 	DECLARE @BL_QUANTITY INT
		DECLARE @OLD_QUANTITY INT
		DECLARE @BL_PRICE FLOAT
		DECLARE @OLD_PRICE FLOAT
		DECLARE @ROW_COUNT INT
		DECLARE @FC_CUSTOMER VARCHAR(100)

		DECLARE @BL_CURSOR CURSOR
 		
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'MONTH' 
		BEGIN
			SET @BL_CURSOR = CURSOR FOR
	 		SELECT CUSTOMER_NAME, PART_NUMBER, BOOKINGMONTH-1, BOOKINGQUARTER-1, BOOKINGYEAR, REM_QTY, 
	 		UNIT_PRICE, END_USER
	 		FROM FN_BACKLOG_FOR_FORECAST_REPORT( @PRODUCT_INPUT, '0', @CUSTOMER_INPUT, '0', @AS_OF_DATE, 'ALL', '0', 'REQDOCKDATE') 
	 		WHERE 
	 			( ( BOOKINGMONTH >= @MONTH_FROM AND BOOKINGYEAR = @YEAR AND BOOKINGMONTH-1 < @TO_MONTH ) OR
	 			  ( BOOKINGMONTH-1 < @MONTH_TO AND BOOKINGYEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
				  ( BOOKINGYEAR < @YEAR_TO AND BOOKINGYEAR > @YEAR ) )
 		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'QUARTER'
		BEGIN
			SET @BL_CURSOR = CURSOR FOR
	 		SELECT CUSTOMER_NAME, PART_NUMBER, BOOKINGMONTH-1, BOOKINGQUARTER-1, BOOKINGYEAR, REM_QTY, 
	 		UNIT_PRICE, END_USER
	 		FROM FN_BACKLOG_FOR_FORECAST_REPORT( @PRODUCT_INPUT, '0', @CUSTOMER_INPUT, '0', @AS_OF_DATE, 'ALL', '0', 'REQDOCKDATE') 
	 		WHERE 
	 			( ( BOOKINGQUARTER >= @MONTH_FROM AND BOOKINGYEAR = @YEAR AND BOOKINGQUARTER-1 < @TO_MONTH ) OR
	 			  ( BOOKINGQUARTER-1 < @MONTH_TO AND BOOKINGYEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
				  ( BOOKINGYEAR < @YEAR_TO AND BOOKINGYEAR > @YEAR ) )
 		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR'
		BEGIN
			SET @BL_CURSOR = CURSOR FOR
	 		SELECT CUSTOMER_NAME, PART_NUMBER, BOOKINGMONTH-1, BOOKINGQUARTER-1, BOOKINGYEAR, REM_QTY, 
	 		UNIT_PRICE, END_USER
	 		FROM FN_BACKLOG_FOR_FORECAST_REPORT( @PRODUCT_INPUT, '0', @CUSTOMER_INPUT, '0', @AS_OF_DATE, 'ALL', '0', 'REQDOCKDATE') 
	 		WHERE  ( BOOKINGYEAR >= @YEAR AND BOOKINGYEAR < @YEAR_TO )
 		END	

 		
 			OPEN @BL_CURSOR 
 				FETCH NEXT FROM @BL_CURSOR
 				INTO @BL_CUSTOMER, @BL_PRODUCT, @BL_MONTH, @BL_QUARTER, @BL_YEAR, @BL_QUANTITY, @BL_PRICE, @BL_END_USER--, @REQ_DATE, @SCH_DATE, @BL_TYPE
 			
 				WHILE @@FETCH_STATUS = 0
 				BEGIN	
					IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'MONTH' 
					BEGIN
	 					SELECT @ROW_COUNT = COUNT(*) FROM @BL_RESULTS
	 					WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR 
	 
	 						IF @ROW_COUNT = 0 
	 					        BEGIN
	 							INSERT INTO @BL_RESULTS VALUES( 0, @BL_CUSTOMER, '0', @BL_PRODUCT, @BL_END_USER, @BL_MONTH, @BL_QUARTER, @BL_YEAR, @BL_QUANTITY, @BL_QUANTITY*@BL_PRICE, '0', '0', '0', '0', '0' )
	 						END
	 						ELSE
	 						BEGIN
	 							SELECT @OLD_QUANTITY = BL_QUANTITY, @OLD_PRICE = BL_PRICE  FROM @BL_RESULTS
	 							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR 
	 							
	 	
	 							UPDATE @BL_RESULTS SET BL_QUANTITY = (@BL_QUANTITY + @OLD_QUANTITY), BL_PRICE = @OLD_PRICE +  ( @BL_QUANTITY*@BL_PRICE )
	 							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR 
							END
						
						IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NOT NULL AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '' AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET BL_QUANTITY = ( SELECT BL_QUANTITY FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR ), BL_PRICE = ( SELECT BL_PRICE FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR )
							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR
						END
						ELSE IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NULL OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '' OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET BL_QUANTITY = ( SELECT BL_QUANTITY FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR ), BL_PRICE = ( SELECT BL_PRICE FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR )
							WHERE PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR
						END
						ELSE
						BEGIN	
							UPDATE @RESULTS SET BL_QUANTITY = ( SELECT BL_QUANTITY FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR ), BL_PRICE = ( SELECT BL_PRICE FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR )
							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND MONTH = @BL_MONTH AND YEAR = @BL_YEAR
						END
					END
					IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'QUARTER'
					BEGIN
	 					SELECT @ROW_COUNT = COUNT(*) FROM @BL_RESULTS
	 					WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR 
	 
	 						IF @ROW_COUNT = 0 
	 					        BEGIN
	 							INSERT INTO @BL_RESULTS VALUES( 0, @BL_CUSTOMER, '0', @BL_PRODUCT, @BL_END_USER, @BL_MONTH, @BL_QUARTER, @BL_YEAR, @BL_QUANTITY, @BL_QUANTITY*@BL_PRICE, '0', '0', '0', '0', '0' )
	 						END
	 						ELSE
	 						BEGIN
	 							SELECT @OLD_QUANTITY = BL_QUANTITY, @OLD_PRICE = BL_PRICE  FROM @BL_RESULTS
	 							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR 
	 							
	 	
	 							UPDATE @BL_RESULTS SET BL_QUANTITY = (@BL_QUANTITY + @OLD_QUANTITY), BL_PRICE = @OLD_PRICE +  ( @BL_QUANTITY*@BL_PRICE )
	 							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR 
							END

						
						IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NOT NULL AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '' AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET BL_QUANTITY = ( SELECT BL_QUANTITY FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR ), BL_PRICE = ( SELECT BL_PRICE FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR )
							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR 						
						END
						ELSE IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NULL OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '' OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET BL_QUANTITY = ( SELECT BL_QUANTITY FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR ), BL_PRICE = ( SELECT BL_PRICE FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR )
							WHERE PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR 						
						END
						ELSE
						BEGIN	
							UPDATE @RESULTS SET BL_QUANTITY = ( SELECT BL_QUANTITY FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR ), BL_PRICE = ( SELECT BL_PRICE FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR )
							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND QUARTER = @BL_QUARTER AND YEAR = @BL_YEAR 						
						END
					END
					IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR'
					BEGIN
						SELECT @ROW_COUNT = COUNT(*) FROM @BL_RESULTS
	 					WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR 
	 
	 						IF @ROW_COUNT = 0 
	 					        BEGIN
	 							INSERT INTO @BL_RESULTS VALUES( 0, @BL_CUSTOMER, '0', @BL_PRODUCT, @BL_END_USER, @BL_MONTH, @BL_QUARTER, @BL_YEAR, @BL_QUANTITY, @BL_QUANTITY*@BL_PRICE, '0', '0', '0', '0', '0' )
	 						END
	 						ELSE
	 						BEGIN
	 							SELECT @OLD_QUANTITY = BL_QUANTITY, @OLD_PRICE = BL_PRICE  FROM @BL_RESULTS
	 							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR 
	 							
	 	
	 							UPDATE @BL_RESULTS SET BL_QUANTITY = (@BL_QUANTITY + @OLD_QUANTITY), BL_PRICE = @OLD_PRICE +  ( @BL_QUANTITY*@BL_PRICE )
	 							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR 
							END
						
						IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NOT NULL AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '' AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET BL_QUANTITY = ( SELECT BL_QUANTITY FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR ), BL_PRICE = ( SELECT BL_PRICE FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR )
							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR 						
						END
						ELSE IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NULL OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '' OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET BL_QUANTITY = ( SELECT BL_QUANTITY FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR ), BL_PRICE = ( SELECT BL_PRICE FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR )
							WHERE PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR 						
						END
						ELSE
						BEGIN	
							UPDATE @RESULTS SET BL_QUANTITY = ( SELECT BL_QUANTITY FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR ), BL_PRICE = ( SELECT BL_PRICE FROM @BL_RESULTS WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR )
							WHERE CUSTOMER = @BL_CUSTOMER AND PRODUCT = @BL_PRODUCT AND ODM = @BL_END_USER AND YEAR = @BL_YEAR 						
						END
					END 
						 														
					FETCH NEXT FROM @BL_CURSOR
 					INTO @BL_CUSTOMER, @BL_PRODUCT, @BL_MONTH, @BL_QUARTER, @BL_YEAR, @BL_QUANTITY, @BL_PRICE, @BL_END_USER--, @REQ_DATE, @SCH_DATE, @BL_TYPE
 				END
 					
 			CLOSE @BL_CURSOR
 			DEALLOCATE @BL_CURSOR

	
	
	-- FOR SHIP DETAILS

	DECLARE @SHIP_CUSTOMER VARCHAR(100)
	DECLARE @SHIP_END_USER VARCHAR(100)
 	DECLARE @SHIP_PRODUCT VARCHAR(50)
	DECLARE @SHIP_SALES_PERSON VARCHAR(100)
	DECLARE @SHIP_MONTH INT
	DECLARE @SHIP_QUARTER INT
 	DECLARE @SHIP_YEAR INT
 	DECLARE @SHIP_QUANTITY INT
	DECLARE @SHIP_OLD_QUANTITY INT
	DECLARE @SHIP_PRICE FLOAT
	DECLARE @SHIP_OLD_PRICE FLOAT
	DECLARE @SHIP_ROW_COUNT INT	
	
	

	DECLARE @SHIP_CURSOR CURSOR

 	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'MONTH' 
		BEGIN
			SET @SHIP_CURSOR = CURSOR FOR
	 		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SHIPMONTH, SHIPQUARTER, SHIPYEAR, QUANTITY, UNIT_PRICE
	 		FROM FN_SHIPPED_ORDERS_FOR_FORECAST_REPORT( @CUSTOMER_INPUT, @PRODUCT_INPUT)
	 		WHERE 
	 			( ( SHIPMONTH >= @MONTH_FROM AND SHIPYEAR = @YEAR AND SHIPMONTH-1 < @TO_MONTH ) OR
	 			  ( SHIPMONTH-1 < @MONTH_TO AND SHIPYEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
				  ( SHIPYEAR < @YEAR_TO AND SHIPYEAR > @YEAR ) )
 		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'QUARTER'
		BEGIN
			SET @SHIP_CURSOR = CURSOR FOR
	 		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SHIPMONTH, SHIPQUARTER, SHIPYEAR, QUANTITY, UNIT_PRICE
	 		FROM FN_SHIPPED_ORDERS_FOR_FORECAST_REPORT( @CUSTOMER_INPUT, @PRODUCT_INPUT)
	 		WHERE 
	 			( ( SHIPQUARTER >= @MONTH_FROM AND SHIPYEAR = @YEAR AND SHIPQUARTER-1 < @TO_MONTH ) OR
	 			  ( SHIPQUARTER-1 < @MONTH_TO AND SHIPYEAR = @YEAR_TO AND @YEAR != @YEAR_TO) OR
				  ( SHIPYEAR < @YEAR_TO AND SHIPYEAR > @YEAR ) )
 		END	
		IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR'
		BEGIN
			SET @SHIP_CURSOR = CURSOR FOR
	 		SELECT CUSTOMER, PRODUCT, SALES_PERSON, END_USER, SHIPMONTH, SHIPQUARTER, SHIPYEAR, QUANTITY, UNIT_PRICE
	 		FROM FN_SHIPPED_ORDERS_FOR_FORECAST_REPORT( @CUSTOMER_INPUT, @PRODUCT_INPUT)
	 		WHERE  ( SHIPYEAR >= @YEAR AND SHIPYEAR < @YEAR_TO )
 		END	


	 	OPEN @SHIP_CURSOR	
	 		FETCH NEXT FROM @SHIP_CURSOR
	 		INTO @SHIP_CUSTOMER, @SHIP_PRODUCT, @SHIP_SALES_PERSON, @SHIP_END_USER, @SHIP_MONTH, @SHIP_QUARTER, @SHIP_YEAR, @SHIP_QUANTITY, @SHIP_PRICE
	
			WHILE @@FETCH_STATUS = 0
 			BEGIN	
	
				
				IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'MONTH' 
				BEGIN    
 					SELECT @SHIP_ROW_COUNT = COUNT(*) FROM @SHIP_RESULTS
 					WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR 
 					
 						IF @SHIP_ROW_COUNT = 0 
 					        BEGIN
 							INSERT INTO @SHIP_RESULTS VALUES( 0, @SHIP_CUSTOMER, '0', @SHIP_PRODUCT, @SHIP_END_USER, @SHIP_MONTH, 0 , @SHIP_YEAR, @SHIP_QUANTITY, @SHIP_QUANTITY*@SHIP_PRICE, '0', '0', '0', '0', '0' )
 						END
 						ELSE
 						BEGIN
 							SELECT @SHIP_OLD_QUANTITY = ACTUAL_QUANTITY, @SHIP_OLD_PRICE = ACTUAL_PRICE  FROM @SHIP_RESULTS
 							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR 
 							
 	
 							UPDATE @SHIP_RESULTS SET ACTUAL_QUANTITY = (@SHIP_QUANTITY + @SHIP_OLD_QUANTITY), ACTUAL_PRICE = @SHIP_OLD_PRICE +  ( @SHIP_QUANTITY*@SHIP_PRICE )
 							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR 
						END
					
						IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NOT NULL AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '' AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET ACTUAL_QUANTITY = ( SELECT ACTUAL_QUANTITY FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR ), ACTUAL_PRICE = ( SELECT ACTUAL_PRICE FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR )
							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR
						END
						ELSE IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NULL OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '' OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET ACTUAL_QUANTITY = ( SELECT ACTUAL_QUANTITY FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR ), ACTUAL_PRICE = ( SELECT ACTUAL_PRICE FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR )
							WHERE PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR
						END
						ELSE
						BEGIN	
							UPDATE @RESULTS SET ACTUAL_QUANTITY = ( SELECT ACTUAL_QUANTITY FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR ), ACTUAL_PRICE = ( SELECT ACTUAL_PRICE FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR )
							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND MONTH = @SHIP_MONTH AND YEAR = @SHIP_YEAR
						END
				END
				IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'QUARTER' 
				BEGIN   
 					SELECT @SHIP_ROW_COUNT = COUNT(*) FROM @SHIP_RESULTS
 					WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR 

 		 				IF @SHIP_ROW_COUNT = 0 
 					        BEGIN
 							INSERT INTO @SHIP_RESULTS VALUES( 0, @SHIP_CUSTOMER, '0', @SHIP_PRODUCT, @SHIP_END_USER, @SHIP_MONTH, @SHIP_QUARTER , @SHIP_YEAR, @SHIP_QUANTITY, @SHIP_QUANTITY*@SHIP_PRICE, '0', '0', '0', '0', '0' )
 						END
						ELSE
 						BEGIN
 							SELECT @SHIP_OLD_QUANTITY = ACTUAL_QUANTITY, @SHIP_OLD_PRICE = ACTUAL_PRICE  FROM @SHIP_RESULTS
 							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR 
 							
 	
 							UPDATE @SHIP_RESULTS SET ACTUAL_QUANTITY = (@SHIP_QUANTITY + @SHIP_OLD_QUANTITY), ACTUAL_PRICE = @SHIP_OLD_PRICE +  ( @SHIP_QUANTITY*@SHIP_PRICE )
 							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR 
						END
 										
						IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NOT NULL AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '' AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET ACTUAL_QUANTITY = ( SELECT ACTUAL_QUANTITY FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR ), ACTUAL_PRICE = ( SELECT ACTUAL_PRICE FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR )
							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR
						END
						ELSE IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NULL OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '' OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET ACTUAL_QUANTITY = ( SELECT ACTUAL_QUANTITY FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR ), ACTUAL_PRICE = ( SELECT ACTUAL_PRICE FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR )
							WHERE PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR
						END
						ELSE
						BEGIN	
							UPDATE @RESULTS SET ACTUAL_QUANTITY = ( SELECT ACTUAL_QUANTITY FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR ), ACTUAL_PRICE = ( SELECT ACTUAL_PRICE FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR )
							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR
						END
				END
				IF @BYPERIOD IS NOT NULL AND LTRIM(RTRIM(@BYPERIOD)) = 'YEAR' 
				BEGIN   
 					SELECT @SHIP_ROW_COUNT = COUNT(*) FROM @SHIP_RESULTS
 					WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND YEAR = @SHIP_YEAR 

 		 				IF @SHIP_ROW_COUNT = 0 
 					        BEGIN
 							INSERT INTO @SHIP_RESULTS VALUES( 0, @SHIP_CUSTOMER, '0', @SHIP_PRODUCT, @SHIP_END_USER, @SHIP_MONTH, @SHIP_QUARTER , @SHIP_YEAR, @SHIP_QUANTITY, @SHIP_QUANTITY*@SHIP_PRICE, '0', '0', '0', '0', '0' )
 						END
						ELSE
 						BEGIN
 							SELECT @SHIP_OLD_QUANTITY = ACTUAL_QUANTITY, @SHIP_OLD_PRICE = ACTUAL_PRICE  FROM @SHIP_RESULTS
 							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND YEAR = @SHIP_YEAR 
 							
 	
 							UPDATE @SHIP_RESULTS SET ACTUAL_QUANTITY = (@SHIP_QUANTITY + @SHIP_OLD_QUANTITY), ACTUAL_PRICE = @SHIP_OLD_PRICE +  ( @SHIP_QUANTITY*@SHIP_PRICE )
 							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND YEAR = @SHIP_YEAR 
						END
 						
						IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NOT NULL AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '' AND LTRIM(RTRIM(@CUSTOMER_SEARCH)) <> '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET ACTUAL_QUANTITY = ( SELECT ACTUAL_QUANTITY FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR ), ACTUAL_PRICE = ( SELECT ACTUAL_PRICE FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR )
							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND YEAR = @SHIP_YEAR
						END
						ELSE IF( ( @PRODUCT_SEARCH IS NOT NULL AND LTRIM(RTRIM(@PRODUCT_SEARCH)) <> '' ) AND ( @CUSTOMER_SEARCH IS NULL OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '' OR LTRIM(RTRIM(@CUSTOMER_SEARCH)) = '-2' ) )
						BEGIN	
							UPDATE @RESULTS SET ACTUAL_QUANTITY = ( SELECT ACTUAL_QUANTITY FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR ), ACTUAL_PRICE = ( SELECT ACTUAL_PRICE FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR )
							WHERE PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND YEAR = @SHIP_YEAR
						END
						ELSE
						BEGIN	
							UPDATE @RESULTS SET ACTUAL_QUANTITY = ( SELECT ACTUAL_QUANTITY FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR ), ACTUAL_PRICE = ( SELECT ACTUAL_PRICE FROM @SHIP_RESULTS WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND QUARTER = @SHIP_QUARTER AND YEAR = @SHIP_YEAR )
							WHERE CUSTOMER = @SHIP_CUSTOMER AND PRODUCT = @SHIP_PRODUCT AND ODM = @SHIP_END_USER AND YEAR = @SHIP_YEAR
						END
				END
				
					
				FETCH NEXT FROM @SHIP_CURSOR
	 			INTO @SHIP_CUSTOMER, @SHIP_PRODUCT, @SHIP_SALES_PERSON, @SHIP_END_USER, @SHIP_MONTH, @SHIP_QUARTER, @SHIP_YEAR, @SHIP_QUANTITY, @SHIP_PRICE
			END 			
		
	 	CLOSE @SHIP_CURSOR
	 	DEALLOCATE @SHIP_CURSOR	


	-- Updation of part number with corresponding marketing part number

	OPEN FORECASTCURSOR 
 			FETCH NEXT FROM FORECASTCURSOR
 			INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE
 			WHILE @@FETCH_STATUS = 0
 			BEGIN

				
				SET @SF_PRODUCT_NAME = NULL
				
				SELECT @SF_PRODUCT_NAME = PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = @SF_PRODUCT
				 					
				SELECT @MKTG_PART_NUMBER = PRODUCT.P_MARKETING_PART_NUMBER FROM PRODUCT,PROCESS_STEP  WHERE PRODUCT.PRODUCT_ID = @SF_PRODUCT AND PRODUCT.PROCESSSTEP_ID IN (SELECT PROCESSSTEP_ID FROM PROCESS_STEP WHERE PS_TYPE_NAME LIKE 'FINISHED GOODS') AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID  
				UPDATE @RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE PRODUCT = @SF_PRODUCT_NAME
 					
 				FETCH NEXT FROM FORECASTCURSOR
 				INTO @SF_BUILDNO, @SF_SPID, @SF_CUSTOMER, @SF_PROJECT, @SF_PRODUCT, @SF_ODM, @SF_MONTH, @SF_QUARTER, @SF_YEAR, @SF_QUANTITY, @SF_PRICE, @SF_USERLIST1, @SF_USERDATE1, @SF_USERTXT1, @SF_USERTXT2, @SF_CONFIDENCE_LEVEL, @SF_FORECAST_TYPE				
 			END	
 		 																
 	CLOSE FORECASTCURSOR
 	DEALLOCATE FORECASTCURSOR 



	UPDATE @RESULTS SET ODM = NULL WHERE ODM = ''	

	SELECT * FROM @RESULTS


 END
 
 







